package com.wmlt.dao.impl;

import com.wmlt.dao.ArticleDao;
import com.wmlt.entity.Article;
import com.wmlt.entity.User;
import com.wmlt.util.JdbcUtil;
import com.wmlt.util.MarkDownUtil;
import org.codehaus.jackson.map.MapperConfig;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

public class ArticleDaoImpl implements ArticleDao {
    String sql = "";
    PreparedStatement prea = null;
    ResultSet res = null;
    Article article = null;
    int result = 0;
    List<Article> articleList = null;

    //获取所有文章

    /**
     * @description: 获取所有文章
     * @param:
     * @return: int result
     * @author liyuxiang
     * @date: 2021/3/22 8:57 下午
     */
    Connection connection = null;
    @Override
    public List<Article> getAllArticle() {
        try {
            sql = "select * from Article as a,`User` as u where u.user_id=a.article_author";
            connection=JdbcUtil.getConnection();
            prea = connection.prepareStatement(sql);
            res = prea.executeQuery();
            if (res != null) {
                articleList = new ArrayList<Article>();
            }
            while (res.next()) {
                result = res.getInt(1);
                User user=new User(res.getString(22), res.getString(13), null, res.getString(15), res.getString(23), res.getString(29), res.getString(30), res.getString(27), res.getString(24), res.getDouble(17));
                article = new Article(res.getInt(1), res.getString(2), res.getString(3), res.getString(4), res.getString(5), res.getString(6), res.getString(7), res.getString(8), res.getInt(9), res.getInt(10), res.getString(11));
                article.setUser(user);
                articleList.add(article);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(res, prea, connection);
        }
        return articleList;
    }

    /**
     * @description: 删除文章    批处理
     * @param: 文章id数组
     * @return: int result
     * @author liyuxiang
     * @date: 2021/3/22 8:57 下午
     */
    @Override
    public int deleteArticle(String[] articleId) {
        sql = "delete  from Article where article_id in (?";
        StringBuffer sqlbuf = new StringBuffer(sql);
        connection=JdbcUtil.getConnection();
        try {
            //拼接sqlbuf，两种情况
            if (articleId.length == 1) {
                //有1条时， 直接加 "）"结束
                sqlbuf.append(")");
            } else {
                //有多条时候，拼接length-1 次，因为初始sql语句已经有一次了。"delete  from `user` where u_id in (? "
                for (int i = 0; i < articleId.length - 1; i++) {
                    sqlbuf.append(",?");

                }
                //最后拼接" )"  圆满完成拼接sql
                sqlbuf.append(")");
            }
            System.out.println(sqlbuf.toString());
            this.prea = connection.prepareStatement(sqlbuf.toString());
            if (articleId.length == 1) {
                prea.setString(1, articleId[0]);
            } else {

                for (int i = 1; i <= articleId.length; i++) {
                    prea.setString(i, articleId[i - 1]);
                    //userids[i-1] 因为数据下标从0 开始。
                }
            }
            return prea.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JdbcUtil.close(null, prea, connection);
        }
        return 0;
    }

    public native void aas();

    /**
     * @description: 查询文章
     * @param: 文章id
     * @return: int result
     * @author liyuxiang
     * @date: 2021/3/22 8:56 下午
     */
    @Override
    public List<Article> queryArticle(String articleTitle) {
        connection=JdbcUtil.getConnection();
        try {
            sql = "select * from Article where INSTR(article_title, ?)";
            prea = connection.prepareStatement(sql);
            prea.setString(1, articleTitle);
            res = prea.executeQuery();
            if (res != null) {
                articleList = new ArrayList<Article>();
            }
            while (res.next()) {
                result = res.getInt(1);
                article = new Article(res.getInt("article_id"), res.getString("article_title"), res.getString("article_img"), res.getString("article_author"), res.getString("author_img"), res.getString("article_content"), res.getString("article_type"), res.getString("article_time"), res.getInt("article_like"), res.getInt("article_browse"), res.getString("article_status"));
                articleList.add(article);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(res, prea, connection);
        }
        return articleList;
    }

    //获取文章
    @Override
    public List<Article> getToObtain() {
        connection=JdbcUtil.getConnection();
        List<Article> articleList = null;
        // TODO Auto-generated method stub
        try {
            String sql = "SELECT * FROM `Article` where article_status=\"审核通过\" and article_type!=5 ORDER BY article_like desc limit 0,4";
            prea = connection.prepareStatement(sql);

            res = prea.executeQuery();

            while (res.next()) {
                if (articleList == null) {
                    articleList = new ArrayList<Article>();
                }
                int articleId = res.getInt("article_id");
                String articleTitle = res.getString("article_title");

                /*SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String articleTime = sim.format(res.getDate("article_time"));*/
                String articleImg = res.getString("article_img");
                String articleAuthor = res.getString("article_author");
                String authorImg = res.getString("author_img");
                String articleContent = res.getString("article_content");
                int articleType = res.getInt("article_type");
                String articleTypes=res.getString("article_type");
                int articleLike = res.getInt("article_like");
                int articleBrowse = res.getInt("article_browse");

                Article article = new Article();
                article.setArticleId(articleId);
                article.setArticleAuthor(articleAuthor);
                article.setArticleBrowse(articleBrowse);
                article.setArticleContent(MarkDownUtil.markdownToHtml(articleContent));
                article.setArticleImg(articleImg);
                article.setArticleLike(articleLike);
                //article.setArticleTime(articleTime);
                article.setArticleType(articleTypes);
                article.setAuthorImg(authorImg);
                article.setArticleTitle(articleTitle);

                articleList.add(article);

            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            // TODO: handle finally clause

            JdbcUtil.close(res, prea, connection);

        }

        return articleList;// TODO Auto-generated method stub
    }

    @Override
    public String getArticleType(int articletype) {
        connection=JdbcUtil.getConnection();
        String articleType = null;
        PreparedStatement pre=null;
        ResultSet resultSet=null;
        try {
            String sql = "select * from Article_type where id=?";
            pre = connection.prepareStatement(sql);
            pre.setInt(1, articletype);
            resultSet = pre.executeQuery();

            while (resultSet.next()) {
                articleType = resultSet.getString("article_type");


            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            // TODO: handle finally clause

            JdbcUtil.close(resultSet, pre, connection);

        }
        return articleType;
    }

    /**
     * @description: 添加文章
     * @param: 文章实体类
     * @return: int result
     * @author liyuxiang
     * @date: 2021/3/22 5:21 下午
     */
    @Override
    public int addArticle(Article article) {
        connection = JdbcUtil.getConnection();
        try {
            sql = "insert into `Article`(article_title,article_content,article_author,author_img,article_time,article_status) values (?,?,?,?,?,?);";
            prea = connection.prepareStatement(sql);
            prea.setString(1, article.getArticleTitle());
            prea.setString(2, article.getArticleContent());
            prea.setString(3, article.getArticleAuthor());
            prea.setString(4,article.getAuthorImg());
            prea.setString(5, article.getArticleTime());
            prea.setString(6, article.getArticleStatus());
            return prea.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JdbcUtil.close(null, prea, null);
        }
        return 0;
    }

    /**
     * @description: 审核文章
     * @param:
     * @return: int result
     * @author liyuxiang
     * @date: 2021/3/22 8:29 下午
     */
    @Override
    public int reviewArticle(int[] articleId) {
        sql = "update Article set article_status='审核通过' where article_id in (?";
        StringBuffer sqlbuf = new StringBuffer(sql);
        connection = JdbcUtil.getConnection();
        try {
            //拼接sqlbuf，两种情况
            if (articleId.length == 1) {
                //有1条时， 直接加 "）"结束
                sqlbuf.append(")");
            } else {
                //有多条时候，拼接length-1 次，因为初始sql语句已经有一次了。"delete  from `user` where u_id in (? "
                for (int i = 0; i < articleId.length - 1; i++) {
                    sqlbuf.append(",?");

                }
                //最后拼接" )"  圆满完成拼接sql
                sqlbuf.append(")");
            }
            System.out.println(sqlbuf.toString());
            this.prea = connection.prepareStatement(sqlbuf.toString());
            if (articleId.length == 1) {
                prea.setInt(1, articleId[0]);
            } else {

                for (int i = 1; i <= articleId.length; i++) {
                    prea.setInt(i, articleId[i - 1]);
                    //userids[i-1] 因为数据下标从0 开始。
                }
            }
            return prea.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JdbcUtil.close(null, prea, null);
        }
        return 0;
    }
    //精选头条

    public List<Article> getTypeLook() {
        connection = JdbcUtil.getConnection();
        List<Article> typeListArticle = null;
        // TODO Auto-generated method stub
        try {

             sql = "SELECT * from Article as a,Article_type as t,`User` as u where a.article_type=t.id and u.user_id=a.article_author and   a.article_status=\"审核通过\" and a.article_type=5 limit 0,4";
            prea = connection.prepareStatement(sql);

            res = prea.executeQuery();

            while (res.next()) {
                if (typeListArticle == null) {
                    typeListArticle = new ArrayList<Article>();
                }
                int articleId = res.getInt(1);
                String articleTitle = res.getString(2);

                /*SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String articleTime = sim.format(res.getDate("article_time"));*/
                String articleImg = res.getString(3);
                String articleAuthor = res.getString(4);
                String authorImg = res.getString(5);
                String articleContent = res.getString(6);
//                int articleType = res.getInt(7);
                String articleTypes=res.getString(12);
                int articleLike = res.getInt(9);
//                System.out.println(res.getInt("article_like"));
                int articleBrowse = res.getInt(10);
                User user=new User(res.getString(24), res.getString(15), null, res.getString(17), res.getString(25), res.getString(31), res.getString(32), res.getString(29), res.getString(26), res.getDouble(19));

                Article article = new Article();
                article.setArticleId(articleId);
                article.setArticleAuthor(articleAuthor);
//                article.setArticleBrowse(articleBrowse);
                article.setArticleContent(articleContent);
                article.setArticleImg(articleImg);
//                article.setArticleLike(articleLike);
                //article.setArticleTime(articleTime);
                article.setArticleType(articleTypes);
                article.setAuthorImg(authorImg);
                article.setArticleTitle(articleTitle);
                article.setUser(user);
                typeListArticle.add(article);

            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            // TODO: handle finally clause

            JdbcUtil.close(res, prea, null);

        }

        return typeListArticle;// TODO Auto-generated method stub
    }
    //根据java类型去获取相对应的文章列表

    @Override
    public List<Article> getTheArticleByType() {
        connection=JdbcUtil.getConnection();
        List<Article> typeListGetArticle = null;
        // TODO Auto-generated method stub
        try {
            String sql = "select * from Article as a,Article_type as t where a.article_type=t.id and a.article_type=1 and a.article_status=\"审核通过\" ORDER BY a.article_like desc limit 0,13";
            prea = connection.prepareStatement(sql);

            res = prea.executeQuery();

            while (res.next()) {
                if (typeListGetArticle == null) {
                    typeListGetArticle = new ArrayList<Article>();
                }
                int articleId = res.getInt("article_id");
                String articleTitle = res.getString("article_title");

                /*SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String articleTime = sim.format(res.getDate("article_time"));*/
                String articleImg = res.getString("article_img");
                String articleAuthor = res.getString("article_author");
                //String authorImg = res.getString("author_img");
                String articleContent = res.getString("article_content");
                int articleType = res.getInt("article_type");
                String articleTypes=res.getString("article_type");
                int articleLike = res.getInt("article_like");
                int articleBrowse = res.getInt("article_browse");

                Article article = new Article();
                article.setArticleId(articleId);
                article.setArticleAuthor(articleAuthor);
                article.setArticleBrowse(articleBrowse);
                article.setArticleContent(articleContent);
                article.setArticleImg(articleImg);
                article.setArticleLike(articleLike);
                //article.setArticleTime(articleTime);
                article.setArticleType(articleTypes);
                //article.setAuthorImg(authorImg);
                article.setArticleTitle(articleTitle);

                typeListGetArticle.add(article);

            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            // TODO: handle finally clause

            JdbcUtil.close(res, prea, null);

        }

        return typeListGetArticle;// TODO Auto-generated method stub
    }
    //根据程序人生获取文章列表

    @Override
    public List<Article> getArticlesAccordingToProgram() {
        connection = JdbcUtil.getConnection();
        List<Article> articleAccordingList = null;
        // TODO Auto-generated method stub
        try {
            String sql = "select * from Article as a,Article_type as t where a.article_type=t.id and a.article_type=6 and a.article_status=\"审核通过\" ORDER BY a.article_like desc limit 0,13";
            prea = connection.prepareStatement(sql);

            res = prea.executeQuery();

            while (res.next()) {
                if (articleAccordingList == null) {
                    articleAccordingList = new ArrayList<Article>();
                }
                int articleId = res.getInt("article_id");
                String articleTitle = res.getString("article_title");

                /*SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String articleTime = sim.format(res.getDate("article_time"));*/
                String articleImg = res.getString("article_img");
                String articleAuthor = res.getString("article_author");
                //String authorImg = res.getString("author_img");
                String articleContent = res.getString("article_content");
                int articleType = res.getInt("article_type");
                String articleTypes=res.getString("article_type");
//                String articleTypes = getArticleType(articleType);
                int articleLike = res.getInt("article_like");
                int articleBrowse = res.getInt("article_browse");

                Article article = new Article();
                article.setArticleId(articleId);
                article.setArticleAuthor(articleAuthor);
                article.setArticleBrowse(articleBrowse);
                article.setArticleContent(articleContent);
                article.setArticleImg(articleImg);
                article.setArticleLike(articleLike);
                //article.setArticleTime(articleTime);
                article.setArticleType(articleTypes);
                //article.setAuthorImg(authorImg);
                article.setArticleTitle(articleTitle);

                articleAccordingList.add(article);

            }
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            // TODO: handle finally clause

            JdbcUtil.close(res, prea, null);

        }

        return articleAccordingList;// TODO Auto-generated method stub
    }

    @Override
    public Article queryArticleByArticleId(int articleId) {
        connection = JdbcUtil.getConnection();
        System.out.println(articleId);
        try {
            sql = "SELECT * from Article as a,`User` as u where a.article_id=? and a.article_author=u.user_id";
            prea = connection.prepareStatement(sql);
            prea.setInt(1, articleId);
            res = prea.executeQuery();
            while(res.next()){
                int articleIds = res.getInt(1);
                String articleTitle = res.getString(2);
                /*SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String articleTime = sim.format(res.getDate("article_time"));*/
                String articleImg = res.getString(3);
                String articleAuthor = res.getString(4);
                String authorImg = res.getString(5);
                String articleContent = res.getString(6);
//                int articleType = res.getInt(7);
                String articleTypes=res.getString(12);
                int articleLike = res.getInt(9);
//                System.out.println(res.getInt("article_like"));
                int articleBrowse = res.getInt(10);
                User user=new User(res.getString(22), res.getString(13), null, res.getString(15), res.getString(23), res.getString(29), res.getString(30), res.getString(27), res.getString(24), res.getDouble(17));

                article = new Article();
                article.setArticleId(articleIds);
                article.setArticleAuthor(articleAuthor);
//                article.setArticleBrowse(articleBrowse);
                MarkDownUtil markDownUtil=new MarkDownUtil(articleContent);
                markDownUtil.start();
                System.out.println(markDownUtil.markdown);
                article.setArticleContent(MarkDownUtil.markdownToHtmlExtensions(markDownUtil.markdown));
                article.setArticleImg(articleImg);
//                article.setArticleLike(articleLike);
                //article.setArticleTime(articleTime);
                article.setArticleType(articleTypes);
                article.setAuthorImg(authorImg);
                article.setArticleTitle(articleTitle);
                article.setUser(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(res, prea, null);
        }
        return article;
    }

    @Override
    public List<Article> getArticleListByUserId(String userId) {
        connection = JdbcUtil.getConnection();
        try {
            sql = "select * from Article where article_author=?";
            prea = connection.prepareStatement(sql);
            prea.setString(1, userId);
            res = prea.executeQuery();
            if (res!=null) articleList=new ArrayList<Article>();
            while (res.next()) {
                article = new Article(res.getInt("article_id"), res.getString("article_title"), res.getString("article_img"), res.getString("article_author"), res.getString("author_img"), res.getString("article_content"), res.getString("article_type"), res.getString("article_time"), res.getInt("article_like"), res.getInt("article_browse"), res.getString("article_status"));
                MarkDownUtil markDownUtil=new MarkDownUtil(res.getString("article_content"));
                markDownUtil.start();
                String theText=MarkDownUtil.markdownToHtmlExtensions(markDownUtil.markdown);
                String plainText =theTextConversion(theText);
                article.setArticleContent(plainText);
                articleList.add(article);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(res, prea, null);
        }
        return articleList;
    }

    //文本转换
    public static String theTextConversion(String aa) {
        String htmlStr = aa; // 含html标签的字符串
        String textStr = "";
        java.util.regex.Pattern p_script;
        java.util.regex.Matcher m_script;
        java.util.regex.Pattern p_style;
        java.util.regex.Matcher m_style;
        java.util.regex.Pattern p_html;
        java.util.regex.Matcher m_html;
        try {
            String regEx_script = "<[\\s]*?script[^>]*?>[\\s\\S]*?<[\\s]*?\\/[\\s]*?script[\\s]*?>"; // 定义script的正则表达式{或<script>]*?>[\s\S]*?<\/script>
// }
            String regEx_style = "<[\\s]*?style[^>]*?>[\\s\\S]*?<[\\s]*?\\/[\\s]*?style[\\s]*?>"; // 定义style的正则表达式{或<style>]*?>[\s\S]*?<\/style>
// }
            String regEx_html = "<[^>]+>"; // 定义HTML标签的正则表达式

            p_script = Pattern.compile(regEx_script, Pattern.CASE_INSENSITIVE);
            m_script = p_script.matcher(htmlStr);
            htmlStr = m_script.replaceAll(""); // 过滤script标签

            p_style = Pattern.compile(regEx_style, Pattern.CASE_INSENSITIVE);
            m_style = p_style.matcher(htmlStr);
            htmlStr = m_style.replaceAll(""); // 过滤style标签

            p_html = Pattern.compile(regEx_html, Pattern.CASE_INSENSITIVE);
            m_html = p_html.matcher(htmlStr);
            htmlStr = m_html.replaceAll(""); // 过滤html标签

            textStr = htmlStr;
            System.out.println(textStr);
        } catch (Exception e) {
            System.err.println("Html2Text: " + e.getMessage());
        }

        return textStr;
    }
    @Override
    public int increaseArticleReading(String articleId, int number) {
        connection=JdbcUtil.getConnection();
        try{
            sql="update Article set article_browse=article_browse+? where article_id=?";
            prea=connection.prepareStatement(sql);
            prea.setInt(1,number);
            prea.setString(2,articleId);
            result = prea.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtil.close(null,prea,null);
        }
        return result;
    }
}
